package org.lq.shopping.dao.impl;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.shopping.dao.ShopOrderDao;
import org.lq.shopping.entity.ShopOrder;
import org.lq.shopping.util.DruidUtil;

import com.sun.org.apache.bcel.internal.generic.NEW;

/**
 * 订单
 * @author Administrator qyt
 *
 */
public class ShopOrderDaoImpl implements ShopOrderDao {
	QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());

	/**
	 * 添加
	 */
	@Override
	public int add(ShopOrder t) {
		int num = 0;
		String sql = "insert into shop_order\r\n" + 
				"  (order_id, buy_date, price, count, status, user_id, address_id, payid, goods_id)\r\n" + 
				"values\r\n" + 
				"  (order_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?)\r\n" + 
				"";
		try {
			num = qr.execute(sql, t.getBuy_date(),t.getPrice(),t.getCount(),t.getStatus(),t.getUser_id(),t.getAddress_id(),t.getPayId(),t.getGoods_id());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	

	/**
	 * 删除
	 */
	@Override
	public int delete(int id) {
		int num = 0;
		String sql = "delete from shop_order where order_id = ?";
		try {
			num = qr.execute(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	

	/**
	 * 修改
	 */
	@Override
	public int update(ShopOrder t) {
		int num = 0;
		String sql = "update shop_order\r\n" + 
				"   set  buy_date = ?,\r\n" + 
				"       price = ?,\r\n" + 
				"       count = ?,\r\n" + 
				"       status = ?,\r\n" + 
				"       user_id = ?,\r\n" + 
				"       address_id = ?,\r\n" + 
				"       payid = ?,\r\n" + 
				"       goods_id = ?\r\n" + 
				" where order_id = ?\r\n";
		try {
			num = qr.execute(sql, t.getBuy_date(),t.getPrice(),t.getCount(),t.getStatus(),t.getUser_id(),t.getAddress_id(),t.getPayId(),t.getGoods_id(),t.getOrder_id());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	/**
	 * 查询全部
	 */
	@Override
	public List<ShopOrder> findAll() {
		List<ShopOrder> orderlist = null;
		String sql = "select * from shop_order";
		try {
			orderlist = qr.query(sql, new BeanListHandler<>(ShopOrder.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orderlist;
	}


	/**
	 * 通过ID查询
	 */
	@Override
	public ShopOrder findById(int id) {
		ShopOrder order = null;
		String sql = "select * from shop_order where order_id = ?";
		try {
			order = qr.query(sql, new BeanHandler<>(ShopOrder.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return order;
	}

	/**
	 * 通过列名模糊查询
	 */
	@Override
	public List<ShopOrder> LikeColumnByValue(String columName, Object value) {
		List<ShopOrder> orderlist = null;
		String sql = "select * from shop_order where "+columName +" like ?";
		try {
			orderlist = qr.query(sql, new BeanListHandler<>(ShopOrder.class),value);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orderlist;
	}

	/**
	 * 总行数
	 */
	@Override
	public int getCount() {
		int count = 0;
		String sql = "select count(1) from shop_order";
		try {
			count = qr.query(sql, new ScalarHandler<BigDecimal>()).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * 分页查询
	 */
	@Override
	public List<ShopOrder> findPage(int pageIndex, int pageSize) {
		List<ShopOrder> orderlist = null;
		int max = pageIndex * pageSize;
		int min = (pageIndex-1) * pageSize;
		String sql = "select * from "
				+ " ( select rownum r , o.* from "
				+ " (select * from shop_order order by order_id) o "
				+ " where rownum <= ? ) "
				+ " where r> ?";
		try {
			orderlist = qr.query(sql, new BeanListHandler<>(ShopOrder.class),max,min);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orderlist;
	}
	
	/**
	 * 根据订单时间范围查询
	 */
	@Override
	public List<ShopOrder> getOrderByTime(Date startTime, Date endTime) {
		List<ShopOrder> orderlist = null;
		String sql = "select * from shop_order where buy_date between to_date(?,'yyyy-mm-dd hh24:mi:ss') and to_date(?,'yyyy-mm-dd hh24:mi:ss')";
		try {
			orderlist = qr.query(sql, new BeanListHandler<>(ShopOrder.class),startTime,endTime);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orderlist;
	}

	/**
	 * 根据用户ID查询定单状态
	 */
	@Override
	public int getStatus(int userId,String status) {
		int num = 0;
		String sql = "select count from shop_order where user_id = ? and status = ? ";
		try {
			num = qr.query(sql,new ScalarHandler<BigDecimal>()).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}


	@Override
	public List<ShopOrder> findPageByTime(Date startTime, Date endTime, int pageIndex, int pageSize) {
		List<ShopOrder> orderlist = null;
		int max = pageIndex * pageSize;
		int min = (pageIndex-1) * pageSize;
		String sql = "select * from "
				+ " ( select rownum r , o.* from "
				+ " (select * from shop_order where buy_date between to_date(?,'yyyy-mm-dd hh24:mi:ss') and to_date(?,'yyyy-mm-dd hh24:mi:ss') order by order_id ) o "
				+ " where rownum <= ? ) "
				+ " where r> ?";
		try {
			orderlist = qr.query(sql, new BeanListHandler<>(ShopOrder.class),startTime,endTime,max,min);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orderlist;
	}


	@Override
	public List<ShopOrder> findPageByStatus(String status, int pageIndex, int pageSize) {
		List<ShopOrder> orderlist = null;
		int max = pageIndex * pageSize;
		int min = (pageIndex-1) * pageSize;
		String sql = "select * from "
				+ " ( select rownum r , o.* from "
				+ " (select * from shop_order where status = ? order by order_id) o "
				+ " where rownum <= ? ) "
				+ " where r> ?";
		try {
			orderlist = qr.query(sql, new BeanListHandler<>(ShopOrder.class),status,max,min);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orderlist;
	}

}
